from flask import Flask, render_template, request, redirect, url_for
from flask_sqlalchemy import SQLAlchemy

# 配置 Flask 应用
app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql+pymysql://username:password@localhost/db_name'
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False

# 初始化 SQLAlchemy
db = SQLAlchemy(app)

# 定义数据库模型，以映射到表
class MyTable(db.Model):
    __tablename__ = 'student'
    Sno = db.Column(db.String(20), nullable=False,primary_key=True)
    Sname = db.Column(db.String(20), nullable=False)
    Ssex=db.Column(db.String(20),nullable=False)
    Sage = db.Column(db.Integer, nullable=False)
    Dno = db.Column(db.String(20), nullable=False)
    Sclass = db.Column(db.String(20), nullable=True)
    address = db.Column(db.String(255), nullable=True)

# 路由和视图函数
@app.route('/')
def index():
    # 查询所有记录
    records = MyTable.query.all()
    return render_template('index.html', records=records)

#添加新记录
@app.route('/add', methods=['POST'])
def add_record():
    # 从表单中获取数据
    Sno = request.form['Sno']
    Sname = request.form['Sname']
    Ssex = request.form['Ssex']
    Sage = request.form['Sage']
    Dno = request.form['Dno']
    Sclass= request.form['Sclass']
    address = request.form['address']
    # 添加新记录
    new_record = MyTable(Sno=Sno,Sname=Sname,Ssex=Ssex,Sage=Sage,Dno=Dno,Sclass=Sclass,address=address)

    db.session.add(new_record)
    db.session.commit()
    #重定向到索引页面
    return redirect(url_for('index'))

#更新记录
@app.route('/update/<Sno>', methods=['post','get'])
def update_record(Sno):
    # 查询要更新的记录
    record = MyTable.query.get_or_404(Sno)
    if request.method == 'POST':
        # 从表单中获取数据并更新记录
        record.Sname = request.form['Sname']
        record.Ssex = request.form['Ssex']
        record.Sage = request.form['Sage']
        record.Dno = request.form['Dno']
        record.Sclass = request.form['Sclass']
        record.address = request.form['address']

        # 提交数据库会话以保存修改
        db.session.commit()

        # 重定向到index页面
        return redirect(url_for('index'))

        # 如果是GET请求，渲染更新表单
    return render_template('update.html', record=record)

#删除记录
@app.route('/delete/<Sno>')
def delete_record(Sno):
    # 查询要删除的记录
    record = MyTable.query.get_or_404(Sno)
    # 删除记录
    db.session.delete(record)
    db.session.commit()
    return redirect(url_for('index'))

# 运行
if __name__ == '__main__':
    app.run(debug=True)
